﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql;
using MySql.Data;
using MySql.Data.MySqlClient;

using INSTA_ORM;

namespace INSTA_ORM
{
    public class SqlGenerator
    {
        private DataMapper _dataMapper;

        /// <summary>
        /// Initializes a new instance of the <see cref="SqlGenerator"/> class.
        /// </summary>
        /// <param name="mapper">The mapper.</param>
        /// <remarks></remarks>
        public SqlGenerator(DataMapper mapper)
        {
            _dataMapper = mapper;
        }

        /// <summary>
        /// Retourne la chaine de caractere nécessaire à une requête SELECT
        /// </summary>
        /// <returns></returns>
        public string generateSqlForSelect()
        {
            string strFormat = "SELECT * FROM {0} WHERE {1}={2};";
            var strResult = string.Format(strFormat,
                                        _dataMapper.ClassMapping.TableName,
                                        _dataMapper.ClassMapping.Properties["Id"].PropertyFieldName,
                                        ReflectionHelper.GetPropertyValue("Id", _dataMapper));
            return strResult;
        }

        /// <summary>
        /// Retourne la chaine de caractere nécesasire à une requête DELETE
        /// </summary>
        /// <returns></returns>
        public string generateSqlForDelete()
        {
            string strFormat = "DELETE FROM {0} WHERE {1}={2};";
            var strResult = string.Format(strFormat,
                                        _dataMapper.ClassMapping.TableName,
                                        _dataMapper.ClassMapping.Properties["Id"].PropertyFieldName,
                                        ReflectionHelper.GetPropertyValue("Id", _dataMapper));
            return strResult;
        }

        /// <summary>
        /// Retourne la chaine de caractere nécesasire à une requête INSERT
        /// </summary>
        /// <returns></returns>
        public string generateSqlForInsert()
        {
            List<string> fieldNameList = new List<string>();
            List<string> fieldValueList = new List<string>();
            string strFormat = "INSERT INTO " + _dataMapper.ClassMapping.TableName + " (";
            string[] arrValues = new String[_dataMapper.ClassMapping.Properties.Count - 1];
            int i = 0;
            foreach (var property in _dataMapper.ClassMapping.Properties)
            {
                if (string.Compare(property.Value.PropertyFieldName, "id", StringComparison.OrdinalIgnoreCase) != 0)
                {
                    fieldNameList.Add(property.Value.PropertyFieldName);
                    fieldValueList.Add(Convert.ToString(ReflectionHelper.GetPropertyValue(property.Value.PropertyName, _dataMapper)));
                    arrValues[i] = " \"{" + i + "}\" ";
                    i++;
                }

            }
            String[] fieldNameArray = fieldNameList.ToArray();
            String[] valueArray = fieldValueList.ToArray();
            strFormat += string.Join(", ", fieldNameList);
            strFormat += ") VALUES (";
            // INSERT INTO {0} (col1, col2, col3) VALUES (
            strFormat += string.Join(", ", arrValues);
            strFormat += ");";
            // INSERT INTO {0} (col1, col2, col3) VALUES ({1}, {2}, {3});
            Console.WriteLine(strFormat);
            var strResult = string.Format(strFormat, valueArray);

            return strResult;
        }

        /// <summary>
        /// Retourne la chaine de caractere nécesasire à une requête UPDATE
        /// </summary>
        /// <returns></returns>
        public string generateSqlForUpdate()
        {
            string strModifierElement = null;

            foreach (KeyValuePair<String, PropertyMapping> currentPair in _dataMapper.ClassMapping.Properties)
            {
                String attribut = currentPair.Key;
                PropertyMapping propertyMapping = currentPair.Value;

                string propertyName = propertyMapping.PropertyName;
                string propertyFieldName = propertyMapping.PropertyFieldName;
                var value = ReflectionHelper.GetPropertyValue(propertyName, _dataMapper);
                
                if (strModifierElement == null)
                {
                    strModifierElement += propertyFieldName + " = '" + value + "'";
                }
                else
                {
                    strModifierElement += ", " + propertyFieldName + " = '" + value + "'";
                }
            }

            String strResult = "UPDATE " + _dataMapper.ClassMapping.TableName + " " + 
                               "SET "    + strModifierElement + " " + 
                               "WHERE "  +  _dataMapper.ClassMapping.Properties["Id"].PropertyFieldName + 
                               " = '"    + ReflectionHelper.GetPropertyValue("Id", _dataMapper) + "'";

            return strResult;
        }
    }
}
